from sqlalchemy import exc, and_, or_
from sqlalchemy.orm import Session, joinedload

from model.TeachingModel import StudentModel
from schema.QuerySchema import QueryBase, StudentQuery
from schema.TeachingSchema import StudentInfo
from util.ErrorCode import ErrorCode
from util.LogHelper import Log


def add_student(studentInfo: StudentInfo, db: Session):
    studentModel = StudentModel(name=studentInfo.name, address=studentInfo.address, age=studentInfo.age,
                                sex=studentInfo.sex, class_id=studentInfo.class_id, phone=studentInfo.phone,
                                email=studentInfo.email, school=studentInfo.school, school_type=studentInfo.school_type,
                                major=studentInfo.major, graduation_date=studentInfo.graduation_date,
                                is_monitor=studentInfo.is_monitor, has_graduation_cert=studentInfo.has_graduation_cert,
                                status=studentInfo.status, contact_person_phone=studentInfo.contact_person_phone,
                                sale_id=studentInfo.sale_id, source=studentInfo.source,
                                referrer=studentInfo.referrer, pay_type=studentInfo.pay_type,
                                loan_amount=studentInfo.loan_amount, pay_amount=studentInfo.pay_amount)
    try:
        db.add(studentModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_student failed:{studentInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_student(studentInfo: StudentInfo, db: Session):
    studentModel = db.query(StudentModel).filter(StudentModel.id == studentInfo.id).one_or_none()
    if studentModel is not None:
        if studentInfo.name is not None:
            studentModel.name = studentInfo.name if len(studentInfo.name) != 0 else studentModel.name
        if studentInfo.address is not None:
            studentModel.address = studentInfo.address if len(studentInfo.address) != 0 else studentModel.address
        if studentInfo.age is not None:
            studentModel.age = studentInfo.age if len(studentInfo.age) != 0 else studentModel.age
        if studentInfo.sex != 0:
            studentModel.sex = studentInfo.sex
        if studentInfo.class_id != 0:
            studentModel.class_id = studentInfo.class_id
        if studentInfo.phone is not None:
            studentModel.phone = studentInfo.phone if len(studentInfo.phone) != 0 else studentModel.phone
        if studentInfo.email is not None:
            studentModel.email = studentInfo.email if len(studentInfo.email) != 0 else studentModel.email
        if studentInfo.school is not None:
            studentModel.school = studentInfo.school if len(studentInfo.school) != 0 else studentModel.school
        if studentInfo.school_type != 0:
            studentModel.school_type = studentInfo.school_type
        if studentInfo.major is not None:
            studentModel.major = studentInfo.major if len(studentInfo.major) != 0 else studentModel.major
        if studentInfo.graduation_date is not None:
            studentModel.graduation_date = studentInfo.graduation_date if len(
                studentInfo.graduation_date) != 0 else studentModel.graduation_date
        if studentInfo.has_graduation_cert is not None:
            studentModel.has_graduation_cert = studentInfo.has_graduation_cert
        if studentInfo.contact_person_phone is not None:
            studentModel.contact_person_phone = studentInfo.contact_person_phone if len(
                studentInfo.contact_person_phone) != 0 else studentModel.contact_person_phone
        if studentInfo.status != 0:
            studentModel.status = studentInfo.status
        if studentInfo.sale_id != 0:
            studentModel.sale_id = studentInfo.sale_id
        if studentInfo.source != 0:
            studentModel.source = studentInfo.source
        if studentInfo.referrer is not None:
            studentModel.referrer = studentInfo.referrer if len(studentInfo.referrer) != 0 else studentModel.referrer
        if studentInfo.pay_type != 0:
            studentModel.pay_type = studentInfo.pay_type
        if studentInfo.loan_amount != 0:
            studentModel.loan_amount = studentInfo.loan_amount
        if studentInfo.pay_amount != 0:
            studentModel.pay_amount = studentInfo.pay_amount

        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_student failed:{studentInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'id:{studentInfo.id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_student_by_page(query_base: QueryBase, db: Session):
    try:
        total_count = db.query(StudentModel).count()
        total_pages = (total_count + query_base.limit - 1) // query_base.limit

        result = db.query(StudentModel).options(joinedload(StudentModel.class_info),
                                                joinedload(StudentModel.sale_info)).offset(
            (query_base.skip - 1) * query_base.limit).limit(query_base.limit).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_student_by_page failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


# class_id、name、school_type、has_graduation_cert、status、sale_id、source、pay_type查询student
def query_student_by_field(studentQuery: StudentQuery, db: Session):
    try:
        if studentQuery.name is not None and len(studentQuery.name) == 0:
            studentQuery.name = None

        total_count = db.query(StudentModel).filter(and_(
            or_(StudentModel.name == studentQuery.name, studentQuery.name is None),
            or_(StudentModel.class_id == studentQuery.class_id, studentQuery.class_id == 0),
            or_(StudentModel.school_type == studentQuery.school_type, studentQuery.school_type == 0),
            or_(StudentModel.has_graduation_cert == studentQuery.has_graduation_cert,
                studentQuery.has_graduation_cert is None),
            or_(StudentModel.status == studentQuery.status, studentQuery.status == 0),
            or_(StudentModel.sale_id == studentQuery.sale_id, studentQuery.sale_id == 0),
            or_(StudentModel.source == studentQuery.source, studentQuery.source == 0),
            or_(StudentModel.pay_type == studentQuery.pay_type, studentQuery.pay_type == 0)
        )).count()

        total_pages = (total_count + studentQuery.query_base.limit - 1) // studentQuery.query_base.limit

        result = db.query(StudentModel).options(joinedload(StudentModel.class_info),
                                                joinedload(StudentModel.sale_info)).filter(and_(
            or_(StudentModel.name == studentQuery.name, studentQuery.name is None),
            or_(StudentModel.class_id == studentQuery.class_id, studentQuery.class_id == 0),
            or_(StudentModel.school_type == studentQuery.school_type, studentQuery.school_type == 0),
            or_(StudentModel.has_graduation_cert == studentQuery.has_graduation_cert,
                studentQuery.has_graduation_cert is None),
            or_(StudentModel.status == studentQuery.status, studentQuery.status == 0),
            or_(StudentModel.sale_id == studentQuery.sale_id, studentQuery.sale_id == 0),
            or_(StudentModel.source == studentQuery.source, studentQuery.source == 0),
            or_(StudentModel.pay_type == studentQuery.pay_type, studentQuery.pay_type == 0)
        )).offset((studentQuery.query_base.skip - 1) * studentQuery.query_base.limit).limit(
            studentQuery.query_base.limit).all()

        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_student_by_field failed! {studentQuery}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_student_by_class_id(class_id: int, db: Session):
    try:
        result = db.query(StudentModel).filter(StudentModel.class_id == class_id).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_student_by_page failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_student_by_phone_and_classid(phone: str, class_id: int, db: Session):
    try:
        result = db.query(StudentModel).filter(
            and_(StudentModel.phone == phone, StudentModel.class_id == class_id)).one_or_none()
        if result is not None:
            return True
        else:
            return False
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(
            f'query_student_by_phone_and_classid failed! phone: {phone}, class_id: {class_id}  message: {e}')
        return False


def set_monitor(student_id: int, is_monitor: int, db: Session):
    studentModel = db.query(StudentModel).filter(StudentModel.id == student_id).one_or_none()
    if studentModel is not None:
        if studentModel.is_monitor != is_monitor:
            studentModel.is_monitor = is_monitor
            try:
                db.commit()
                return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': studentModel}
            except exc.SQLAlchemyError as e:
                Log.getlogger().error(f'set_monitor failed! id: {student_id} message: {e}')
                return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
        else:
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': None}
    else:
        Log.getlogger().error(f'id:{student_id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_student_by_id(student_id: int, db: Session):
    try:
        studentModel = db.query(StudentModel).options(joinedload(StudentModel.class_info),
                                                      joinedload(StudentModel.sale_info)).filter(
            StudentModel.id == student_id).one_or_none()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': studentModel}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(
            f'query_student_class_id_by_id failed! student_id: {id}, message: {e}')
        return False


def query_student_class_id_by_id(student_id: int, db: Session):
    try:
        studentModel = db.query(StudentModel).filter(StudentModel.id == student_id).one_or_none()
        if studentModel is not None:
            return studentModel.class_id
        else:
            return None
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(
            f'query_student_class_id_by_id failed! student_id: {student_id}, message: {e}')
        return False
